package com.ph.irp.stock.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.model.PStockStockinvoice;
import com.ph.irp.stock.dao.StockInvoiceDAO;
import com.ph.util.DBUtil;
import com.ph.util.DateUtil;

public class StockInvoiceDAOImpl implements StockInvoiceDAO{
	private ComboPooledDataSource dataSource;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	@Override
	public void addStockInvoice(PStockStockinvoice stockInvoiceModel)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.append("insert into p_stock_stockinvoice");
			sBuilder
					.append("(SupplierID,currencyId,SourceBillType,StockWay,InvoiceNO,TaxCode,InvoiceDate,PayDate,Remark,ManagerID,DepartmentID,BusinesserID," +
							"Ticketer,Fhooker,MakeBiller,IsAuditing,Auditinger,BookKeeper,AuditingDate,FhookDate,IsCancel,IsHook,BillType,tag,checktag)");
			sBuilder.append(" values (");
			sBuilder.append(stockInvoiceModel.getSupplierId());
			sBuilder.append(",");
			sBuilder.append(stockInvoiceModel.getCurrencyId());
			sBuilder.append(",");
			sBuilder.append(stockInvoiceModel.getSourceBillType());
			sBuilder.append(",");
			sBuilder.append(stockInvoiceModel.getStockWay());
			sBuilder.append(",");
			sBuilder.append("'"+stockInvoiceModel.getInvoiceNo()+"'");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getTaxCode()!=null)
				sBuilder.append("'"+stockInvoiceModel.getTaxCode()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			sBuilder.append("'"+stockInvoiceModel.getInvoiceDate()+"'");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getPayDate()!=null)
				sBuilder.append("'"+stockInvoiceModel.getPayDate()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getRemark()!=null)
				sBuilder.append("'"+stockInvoiceModel.getRemark()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getManagerId()!=0)
				sBuilder.append(stockInvoiceModel.getManagerId());
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getDepartmentId()!=0)
				sBuilder.append(stockInvoiceModel.getDepartmentId());
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getBusinessId()!=0)
				sBuilder.append(stockInvoiceModel.getBusinessId());
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getTicketer()!=null)
				sBuilder.append("'"+stockInvoiceModel.getTicketer()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getFhooker()!=null)
				sBuilder.append("'"+stockInvoiceModel.getFhooker()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getMakeBiller()!=null)
				sBuilder.append("'"+stockInvoiceModel.getMakeBiller()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			sBuilder.append(stockInvoiceModel.getIsAuditing());
			sBuilder.append(",");
			if(stockInvoiceModel.getAuditinger()!=null)
				sBuilder.append("'"+stockInvoiceModel.getAuditinger()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getBookKeeper()!=null)
				sBuilder.append("'"+stockInvoiceModel.getBookKeeper()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getAuditingDate()!=null)
				sBuilder.append("'"+stockInvoiceModel.getAuditingDate()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			if(stockInvoiceModel.getFhookDate()!=null)
				sBuilder.append("'"+stockInvoiceModel.getFhookDate()+"'");
			else
				sBuilder.append("null");
			sBuilder.append(",");
			
			sBuilder.append(stockInvoiceModel.getIsCancel());
			sBuilder.append(",");
			sBuilder.append(stockInvoiceModel.getIsHook());
			sBuilder.append(",");
			sBuilder.append(stockInvoiceModel.getBillType());
			sBuilder.append(",");
			sBuilder.append(stockInvoiceModel.getTag());
			sBuilder.append(",");
			sBuilder.append(stockInvoiceModel.getChecktag());
			sBuilder.append(")");
				System.out.println(sBuilder.toString());
			con = dataSource.getConnection();

//			pStmt = con.prepareStatement(sBuilder.toString());
//			
//			pStmt.executeUpdate();
			
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sBuilder.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		// TODO Auto-generated method stub
		
	}

	@Override
	public void deleteStockInvoice(int stockInvoiceID) throws Throwable {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<PStockStockinvoice> getAllStockInvoiceList() throws Throwable {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PStockStockinvoice> stockInvoiceList = new ArrayList<PStockStockinvoice>();
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_stock_stockinvoice");
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PStockStockinvoice stockInvoice = new PStockStockinvoice();
				stockInvoice.setAuditingDate(DateUtil.date2String(rs.getDate("AuditingDate"),"yyyy-MM-dd"));
				stockInvoice.setAuditinger(rs.getString("Auditinger"));
				stockInvoice.setBillType(rs.getInt("BillType"));
				stockInvoice.setBusinessId(rs.getInt("BusinesserID"));
				stockInvoice.setChecktag(rs.getInt("checktag"));
				stockInvoice.setCurrencyId(rs.getInt("currencyId"));
				stockInvoice.setDepartmentId(rs.getInt("DepartmentID"));
				stockInvoice.setFhookDate(DateUtil.date2String(rs.getDate("FhookDate"),"yyyy-MM-dd"));
				stockInvoice.setFhooker(rs.getString("Fhooker"));
				stockInvoice.setInvoiceDate(DateUtil.date2String(rs.getDate("InvoiceDate"),"yyyy-MM-dd"));
				stockInvoice.setInvoiceNo(rs.getString("InvoiceNO"));
				stockInvoice.setIsAuditing(rs.getInt("IsAuditing"));
				stockInvoice.setIsCancel(rs.getInt("IsCancel"));
				stockInvoice.setIsHook(rs.getInt("IsHook"));
				stockInvoice.setMakeBiller(rs.getString("MakeBiller"));
				stockInvoice.setManagerId(rs.getInt("ManagerID"));
				stockInvoice.setPayDate(DateUtil.date2String(rs.getDate("PayDate"), "yyyy-MM-dd"));
				stockInvoice.setRemark(rs.getString("Remark"));
				stockInvoice.setSourceBillType(rs.getInt("SourceBillType"));
				stockInvoice.setStockInvoiceId(rs.getInt("StockInvoiceID"));
				stockInvoice.setStockWay(rs.getInt("StockWay"));
				stockInvoice.setSupplierId(rs.getInt("SupplierID"));
				stockInvoice.setTag(rs.getInt("tag"));
				stockInvoice.setTicketer(rs.getString("Ticketer"));
				stockInvoiceList.add(stockInvoice);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return stockInvoiceList;
	}

	@Override
	public PStockStockinvoice getStockInvoiceByID() throws Throwable {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void updateStockInvoice(PStockStockinvoice stockInvoiceModel)
			throws Throwable {
		// TODO Auto-generated method stub
		
	}

	@Override
	public int getStockInvoiceByNo(String invoiceNO) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		int stockInvoiceId = 0;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_stock_stockinvoice where InvoiceNO=?");
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setString(1,invoiceNO);
			rs = pStmt.executeQuery();
			while (rs.next()) {
				stockInvoiceId = rs.getInt("StockInvoiceID");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return stockInvoiceId;
	}

	@Override
	public List<PStockStockinvoice> getStockInvoiceList(
			PStockStockinvoice stockInvoiceModel) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		System.out.println(stockInvoiceModel.getInvoiceDate());
		List<PStockStockinvoice> stockInvoiceList = new ArrayList<PStockStockinvoice>();
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_stock_stockinvoice where 1=1 ");
			if(stockInvoiceModel.getSupplierId()!=0)
				sb.append(" and SupplierID="+stockInvoiceModel.getSupplierId());
			if(stockInvoiceModel.getInvoiceNo()!=null && !stockInvoiceModel.getInvoiceNo().equals(""))
				sb.append(" and InvoiceNO like '%"+stockInvoiceModel.getInvoiceNo()+"%'");
			if(stockInvoiceModel.getIsAuditing()!=2)
				sb.append(" and IsAuditing="+stockInvoiceModel.getIsAuditing());
			if(stockInvoiceModel.getInvoiceDate()!=null && !stockInvoiceModel.getInvoiceDate().equals("")){
				String invoiceDate = stockInvoiceModel.getInvoiceDate();
				String[] ivd = invoiceDate.split("-");
				if(ivd[1].length()==1)
					invoiceDate = ivd[0]+"0"+ivd[1];
				else
					invoiceDate = ivd[0]+ivd[1];
				if(ivd[2].length()==1)
					invoiceDate = invoiceDate+"0"+ivd[2];
				else
					invoiceDate = invoiceDate+ivd[2];
				sb.append(" and InvoiceDate="+invoiceDate);
			}
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PStockStockinvoice stockInvoice = new PStockStockinvoice();
				stockInvoice.setAuditingDate(DateUtil.date2String(rs.getDate("AuditingDate"),"yyyy-MM-dd"));
				stockInvoice.setAuditinger(rs.getString("Auditinger"));
				stockInvoice.setBillType(rs.getInt("BillType"));
				stockInvoice.setBusinessId(rs.getInt("BusinesserID"));
				stockInvoice.setChecktag(rs.getInt("checktag"));
				stockInvoice.setCurrencyId(rs.getInt("currencyId"));
				stockInvoice.setDepartmentId(rs.getInt("DepartmentID"));
				stockInvoice.setFhookDate(DateUtil.date2String(rs.getDate("FhookDate"),"yyyy-MM-dd"));
				stockInvoice.setFhooker(rs.getString("Fhooker"));
				stockInvoice.setInvoiceDate(DateUtil.date2String(rs.getDate("InvoiceDate"),"yyyy-MM-dd"));
				stockInvoice.setInvoiceNo(rs.getString("InvoiceNO"));
				stockInvoice.setIsAuditing(rs.getInt("IsAuditing"));
				stockInvoice.setIsCancel(rs.getInt("IsCancel"));
				stockInvoice.setIsHook(rs.getInt("IsHook"));
				stockInvoice.setMakeBiller(rs.getString("MakeBiller"));
				stockInvoice.setManagerId(rs.getInt("ManagerID"));
				stockInvoice.setPayDate(DateUtil.date2String(rs.getDate("PayDate"), "yyyy-MM-dd"));
				stockInvoice.setRemark(rs.getString("Remark"));
				stockInvoice.setSourceBillType(rs.getInt("SourceBillType"));
				stockInvoice.setStockInvoiceId(rs.getInt("StockInvoiceID"));
				stockInvoice.setStockWay(rs.getInt("StockWay"));
				stockInvoice.setSupplierId(rs.getInt("SupplierID"));
				stockInvoice.setTag(rs.getInt("tag"));
				stockInvoice.setTicketer(rs.getString("Ticketer"));
				stockInvoiceList.add(stockInvoice);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return stockInvoiceList;
	}
}
